<?php
include('include/config.inc.php');
if(!isset($_SESSION['s_activId'.$cfgPack])
  || (isset($_SESSION['s_userType'.$cfgPack]) && isset($cfgFileNames['complainservice'][$_SESSION['s_userType'.$cfgPack]]) 
      && $cfgFileNames['complainservice'][$_SESSION['s_userType'.$cfgPack]] == 0))
{
  $_SESSION['s_urlRedirectDir'] = $_SERVER['REQUEST_URI'];
  header("Location:checkLogin.php");
}
else
{	
  $typeOfCall = "";
  if(isset($_POST['submitBtn']))
  {
  	// Logic to Update Tech. Allocation Process : Starts
  	$idCount    = 0;
  	$deletedId  = array();
  	$targetedDateEdited = $_POST['targetedDateYear']."-".$_POST['targetedDateMonth']."-".$_POST['targetedDateDay'];
  	for($tech=1; $tech<=5; $tech++)
  	{
  		if(isset($_POST['timeSlotId'.$tech]))
  		{
  			for($tSlot=0; $tSlot < count($_POST['timeSlotId'.$tech]); $tSlot++)
  			{
  				$selectDeletedId = "SELECT complainServiceId, newinstallationMasterId, otherServiceId
  				                      FROM techallocation
  	                           WHERE date = '".$targetedDateEdited."'
  	                             AND technicianId = '".$_POST['technicianId'.$tech]."'
  	                             AND timeSlotId = ".$_POST['timeSlotId'.$tech][$tSlot];
          $selectDeletedIdRes = mysql_query($selectDeletedId);
          if($idRow = mysql_fetch_array($selectDeletedIdRes))
          {
          	if($idRow['complainServiceId'] != 0)
          	{
          		$deletedId[$idCount] = $idRow['complainServiceId']."C";
          		$affTable            = 'complainservice';
          		$affTableId          = 'complainServiceId';
          		$affTableRecord      = $idRow['complainServiceId'];
          	}
          	elseif($idRow['newinstallationMasterId'] != 0)
          	{
          		$deletedId[$idCount] = $idRow['newinstallationMasterId']."N";
          		$affTable            = 'newinstallationmaster';
          		$affTableId          = 'newinstallationMasterId';
          		$affTableRecord      = $idRow['newinstallationMasterId'];
          	}
          	else
          	{
          		$deletedId[$idCount] = $idRow['otherServiceId']."O";
          		$affTable            = 'otherservice';
          		$affTableId          = 'otherServiceId';
          		$affTableRecord      = $idRow['otherServiceId'];
          	}
          	$idCount++;
          }
          
          $updateAffService = "UPDATE ".$affTable."
                                  SET techAllocated = 'N'
                                WHERE ".$affTableId." = ".$affTableRecord;
          $updateAffServiceRes = mysql_query($updateAffService);
          
  	      $deleteQuery = "DELETE FROM techallocation
  	                       WHERE date = '".$targetedDateEdited."'
  	                         AND ".$affTableId." = ".$affTableRecord;
          $deleteQueryRes = mysql_query($deleteQuery);  	                         
  	    }
  	  }
  	}
  	
  	//Below Query Checks whether any slot service Id is active with any slot or not : Starts
  	for($arr=0; $arr < count($deletedId); $arr++)
  	{
  		
      if((substr($deletedId[$arr],-1)) == 'C')
      {
        $serviceType = 'complainServiceId';
      }
      elseif((substr($deletedId[$arr],-1)) == 'N')
      {
      	$serviceType = 'newinstallationMasterId';
      }
      elseif((substr($deletedId[$arr],-1)) == 'O')
      {
      	$serviceType = 'newinstallationMasterId';
      }
      $serviceId = substr_replace($deletedId[$arr],"",-1);
  	  $checkSlots = "SELECT complainServiceId, newinstallationMasterId, otherServiceId
  	                   FROM techallocation
  	                  WHERE ".$serviceType." = ".$serviceId."
  	                    AND date = '".$targetedDateEdited."'";
  	  $checkSlotsRes = mysql_query($checkSlots);
  	  $numRows = mysql_num_rows($checkSlotsRes);
  	  if($numRows > 0)
  	  {
  	    while($slotsRow = mysql_fetch_array($checkSlotsRes))
  	    {
  	    	if($slotsRow['complainServiceId'] > 0)
  	    	{
  	    	  $idToUpdate = $slotsRow['complainServiceId'];
  	    	  $tableToUpdate = 'complainservice';
  	    	  $tableIdToUpdate = 'complainServiceId';
  	    	}
  	    	elseif($slotsRow['newinstallationMasterId'] > 0)
  	    	{
  	    	  $idToUpdate = $slotsRow['newinstallationMasterId'];
  	    	  $tableToUpdate = 'newinstallationmaster';
  	    	  $tableIdToUpdate = 'newinstallationMasterId';
  	    	}
  	    	elseif($slotsRow['otherServiceId'] > 0)
  	    	{
  	    	  $idToUpdate = $slotsRow['otherServiceId'];
  	    	  $tableToUpdate = 'otherservice';
  	    	  $tableIdToUpdate = 'otherServiceId';
  	    	}
  	      $updtRmngStatus = "UPDATE ".$tableToUpdate."
  	                            SET techAllocated = 'Y'
  	                          WHERE ".$tableIdToUpdate." = ".$idToUpdate;
          $updtRmngStatusRes = mysql_query($updtRmngStatus);
  	    }
  	  }
  	}
  	//Below Query Checks whether any slot service Id is active with any slot or not : Ends
  	// Logic to Update Tech. Allocation Process : Ends
    for($i=1;$i<=5;$i++)
    {
      if(isset($_POST['timeSlotId'.$i]))
      {
	      for($j=0;$j < count($_POST['timeSlotId'.$i]);$j++)
	      {
	        $date = $_POST['targetedDateYear']."-".$_POST['targetedDateMonth']."-".$_POST['targetedDateDay'];
	        
	        $complainServiceId       = isset($_POST['complainServiceId']) ? $_POST['complainServiceId'] : 0;
	        $newInstallationMasterId = isset($_POST['newInstallationMasterId']) ? $_POST['newInstallationMasterId'] : 0;
	        $otherServiceId          = isset($_POST['otherServiceId']) ? $_POST['otherServiceId'] : 0;
	        
	        $insertTechAlloction = "INSERT INTO techallocation(date,complainServiceId, newinstallationMasterId, otherServiceId, technicianId,
	                                                           timeSlotId)
	                                VALUES ('".$date."','".$complainServiceId."','".$newInstallationMasterId."','".$otherServiceId."',
	                                        '".$_POST['technicianId'.$i]."','".$_POST['timeSlotId'.$i][$j]."')";
	        $insertTechAlloctionRes = mysql_query($insertTechAlloction);
	        if(!$insertTechAlloctionRes)
	          die("Insert Query Not Inserted : ".mysql_error());
	      }
      }
    }
    $updateComplainService = "UPDATE complainservice
	                               SET targetedDate = '".$date."',
	                                   techAllocated = 'Y'
	                             WHERE complainServiceId =" .$complainServiceId;    
	  $updateComplainServiceRes = mysql_query($updateComplainService);
	  
	  $updateNewInstallation = "UPDATE newinstallationmaster
	                               SET installationDate = '".$date."',
	                                   techAllocated = 'Y'
	                             WHERE newinstallationMasterId =" .$newInstallationMasterId;    
	  $updateNewInstallationRes = mysql_query($updateNewInstallation);
	  
	  $updateOtherService = "UPDATE otherservice
	                            SET targetedDate = '".$date."',
	                                techAllocated = 'Y'
	                          WHERE otherServiceId =" .$otherServiceId;    
	  $updateOtherServiceRes = mysql_query($updateOtherService);
	  
//	  if(!$updateComplainServiceRes || !$updateNewInstallationRes || !$updateOtherServiceRes)
//	    die("Insert Query Not Inserted : ".mysql_error());
//	  else
//	    header("Location:techAllocation.php");
  }
      
  $complainCallDetailArray  = array();
  $complainCount = 0;
  $msg           = "";
  if(isset($_GET['otherServiceId']) && $_GET['otherServiceId'] > 0)
  {
    $selectComplainEntry = "SELECT 0 AS complainServiceId, 0 AS newinstallationMasterId, otherservice.otherServiceId, otherservice.targetedDate AS listedDate,
											           otherservice.description, 0 AS amountCollected, party.companyName, 'others' AS type
											      FROM otherservice
                            JOIN party ON party.partyId = otherservice.partyId
											     WHERE otherservice.techAllocated = 'Y' 
											     AND otherservice.otherServiceId = ".$_GET['otherServiceId'];
  }
  elseif(isset($_GET['newInstallationMasterId']) && $_GET['newInstallationMasterId'] > 0)
  {
    $selectComplainEntry = "SELECT 0 AS complainServiceId, newinstallationmaster.newinstallationMasterId, 0 AS otherServiceId,
											             newinstallationmaster.installationDate AS listedDate, 0 AS description, 0 AS amountCollected, party.companyName, 
											            'newInst' AS type
											       FROM newinstallationmaster
											       JOIN party ON party.partyId = newinstallationmaster.partyId
											      WHERE newinstallationmaster.techAllocated = 'Y'
											      AND newinstallationmaster.newinstallationMasterId = ".$_GET['newInstallationMasterId'];
  }
  elseif(isset($_GET['complainServiceId']) && $_GET['complainServiceId'] > 0)
  {
    $selectComplainEntry = "SELECT complainservice.complainServiceId, 0 AS newinstallationMasterId, 0 AS otherServiceId, complainservice.targetedDate AS listedDate,
                                   complainservice.description,complainservice.amountCollected, party.companyName, 'complain' AS type 
				  						        FROM complainservice
					 						        JOIN party ON party.partyId = complainservice.partyId
											       WHERE complainservice.techAllocated = 'Y'
											       AND complainservice.complainServiceId =".$_GET['complainServiceId'];
  }
  else
  {
     $selectComplainEntry = "SELECT 0 AS complainServiceId, 0 AS newinstallationMasterId, otherservice.otherServiceId, otherservice.targetedDate AS listedDate,
											              otherservice.description, 0 AS amountCollected, party.companyName, 'others' AS type
											         FROM otherservice
                               JOIN party ON party.partyId = otherservice.partyId
											        WHERE otherservice.techAllocated = 'Y'
											        UNION
                             SELECT 0 AS complainServiceId, newinstallationmaster.newinstallationMasterId, 0 AS otherServiceId,
											              newinstallationmaster.installationDate AS listedDate, 0 AS description, 0 AS amountCollected, party.companyName, 
											              'newInst' AS type
											         FROM newinstallationmaster
											         JOIN party ON party.partyId = newinstallationmaster.partyId
											        WHERE newinstallationmaster.techAllocated = 'Y'
											        UNION 
		  									     SELECT complainservice.complainServiceId, 0 AS newinstallationMasterId, 0 AS otherServiceId, complainservice.targetedDate AS listedDate,
                                    complainservice.description,complainservice.amountCollected, party.companyName, 'complain' AS type 
				  						         FROM complainservice
					 						         JOIN party ON party.partyId = complainservice.partyId
											        WHERE complainservice.techAllocated = 'Y'";
											     
  }
  $selectComplainEntryResult = mysql_query($selectComplainEntry);
  while($complaneEntryRow = mysql_fetch_array($selectComplainEntryResult))
  {
	  $complainCallDetailArray[$complainCount]['complainServiceId'] = $complaneEntryRow['complainServiceId'];
	  $complainCallDetailArray[$complainCount]['newinstallationMasterId'] = $complaneEntryRow['newinstallationMasterId'];
	  $complainCallDetailArray[$complainCount]['otherServiceId']    = $complaneEntryRow['otherServiceId'];
    $complainCallDetailArray[$complainCount]['listedDate']        = $complaneEntryRow['listedDate'];
	  $complainCallDetailArray[$complainCount]['description']       = $complaneEntryRow['description'];
	  $complainCallDetailArray[$complainCount]['companyName']       = $complaneEntryRow['companyName'];
	  
	  if($complaneEntryRow['type'] == 'complain')
	    $complainCallDetailArray[$complainCount]['typeOfCall']      = "Complain Call";
	  elseif($complaneEntryRow['type'] == 'newInst')
	    $complainCallDetailArray[$complainCount]['typeOfCall']      = "New Installation";
	  else
	    $complainCallDetailArray[$complainCount]['typeOfCall']      = "Others";
	  //$complainCount++;
  }   
  if($complainCount == 0)
    $msg = '<tr><td align="center" colspan="10"> <h2><font color="red"><b>No Pending Technician Allocation ...!</b></h2></font></td></tr>';
    
  //// Selection of data after technician allocated : Start
  $idToListIn1                     = 0;
  $idToListIn2                     = 0;
  $idToListIn3                     = 0;
  $techAllocatedArray             = array();
  $arrayCounter                   = -1;
  $rowCounter                     = 0;
  $techAllocatedComplainServiceId1 = 0;
  $techAllocatedComplainServiceId2 = 0;
  $techAllocatedComplainServiceId3 = 0;
  $techAllocatedTechnicianId      = 0;
  $selectTechAllocated = "SELECT DATE_FORMAT(complainservice.targetedDate,'%d-%m-%Y') AS targetedDate,
                                 techallocation.complainServiceId, 0 AS newinstallationMasterId, 0 AS otherServiceId, techallocation.technicianId,
                                 techallocation.timeSlotId, complainservice.partyId,complainservice.serviceCharge,complainservice.remarks,
                                 timeslot.slotName, technician.firstName, technician.lastName,party.companyName, party.contactPerson1,
                                 party.contactPerson2, party.mobile1, party.mobile2
                            FROM techallocation
                            JOIN complainservice       ON complainservice.complainServiceId = techallocation.complainServiceId
                            JOIN technician            ON technician.technicianId = techallocation.technicianId
                            JOIN timeslot              ON timeslot.timeSlotId     = techallocation.timeSlotId
                            JOIN party                 ON complainservice.partyId = party.partyId
                           UNION
                          SELECT DATE_FORMAT(newinstallationmaster.installationDate,'%d-%m-%Y') AS targetedDate, 0 AS complainServiceId,
                                 techallocation.newinstallationMasterId, 0 AS otherServiceId, techallocation.technicianId, techallocation.timeSlotId,
                                 newinstallationmaster.partyId,0 AS serviceCharge,0 AS remarks, timeslot.slotName,
                                 technician.firstName, technician.lastName, party.companyName, party.contactPerson1, party.contactPerson2,
                                 party.mobile1, party.mobile2
                            FROM techallocation
                            JOIN newinstallationmaster ON newinstallationmaster.newinstallationMasterId = techallocation.newinstallationMasterId
                            JOIN technician            ON technician.technicianId = techallocation.technicianId
                            JOIN timeslot              ON timeslot.timeSlotId     = techallocation.timeSlotId
                            JOIN party                 ON newinstallationmaster.partyId = party.partyId
                           UNION
                          SELECT DATE_FORMAT(otherservice.targetedDate,'%d-%m-%Y') AS targetedDate, 0 AS complainServiceId,
                                 0 AS newinstallationMasterId, otherservice.otherServiceId, techallocation.technicianId, techallocation.timeSlotId,
                                 otherservice.partyId, 0 AS serviceCharge, 0 AS remarks, timeslot.slotName, technician.firstName,
                                 technician.lastName, party.companyName, party.contactPerson1, party.contactPerson2, party.mobile1, party.mobile2
                            FROM techallocation
                            JOIN otherservice          ON otherservice.otherServiceId = techallocation.otherServiceId
                            JOIN technician            ON technician.technicianId = techallocation.technicianId
                            JOIN timeslot              ON timeslot.timeSlotId     = techallocation.timeSlotId
                            JOIN party                 ON otherservice.partyId = party.partyId
                           ORDER BY targetedDate, complainServiceId, newinstallationMasterId";
  $selectTechAllocatedRes = mysql_query($selectTechAllocated);
  while($techAllocatedRow = mysql_fetch_array($selectTechAllocatedRes))
  {
    // To Get weather it is a Complain, New Installation Or Others : Starts
    if($techAllocatedRow['complainServiceId'] != 0)
      $idToListIn1 = $techAllocatedRow['complainServiceId'];
    if($techAllocatedRow['newinstallationMasterId'] != 0)
      $idToListIn2 = $techAllocatedRow['newinstallationMasterId'];
    if($techAllocatedRow['otherServiceId'] != 0)
      $idToListIn3 = $techAllocatedRow['otherServiceId'];
    // To Get weather it is a Complain, New Installation Or Others : Ends

    if($techAllocatedComplainServiceId1 == $idToListIn1 && 
        $techAllocatedComplainServiceId2 == $idToListIn2 && $techAllocatedComplainServiceId3 == $idToListIn3)
    {
      if($techAllocatedTechnicianId == $techAllocatedRow['technicianId'])
      {
        $techAllocatedArray[$arrayCounter]['technicianId'] = $techAllocatedRow['technicianId'];
        $techAllocatedArray[$arrayCounter]['slotName'.$techNumber] .=  ", ".$techAllocatedRow['slotName'];
      }
      else
      {
        $techNumber++;
        $techAllocatedTechnicianId = $techAllocatedRow['technicianId'];
        $techAllocatedArray[$arrayCounter]['technicianId'] = $techAllocatedRow['technicianId'];
        $techAllocatedArray[$arrayCounter]['technicianName'.$techNumber] = $techAllocatedRow['firstName']." ".$techAllocatedRow['lastName'];

        $techAllocatedArray[$arrayCounter]['slotName'.$techNumber] = $techAllocatedRow['slotName'];
      }
    }
    else
    {
      $arrayCounter++;
      $techNumber = 1;
      if($techAllocatedRow['complainServiceId'] != 0)
				$techAllocatedArray[$arrayCounter]['complainServiceId'] = $idToListIn1;
      elseif($techAllocatedRow['newinstallationMasterId'] != 0)
				$techAllocatedArray[$arrayCounter]['complainServiceId'] = $idToListIn2;
      else
				$techAllocatedArray[$arrayCounter]['complainServiceId'] = $idToListIn3;
      
      $techAllocatedArray[$arrayCounter]['companyName']       = $techAllocatedRow['companyName'];
      $techAllocatedArray[$arrayCounter]['targetedDate']      = $techAllocatedRow['targetedDate'];
      $techAllocatedArray[$arrayCounter]['contactPerson1']    = $techAllocatedRow['contactPerson1'];
      $techAllocatedArray[$arrayCounter]['mobile1']           = $techAllocatedRow['mobile1'];
      $techAllocatedArray[$arrayCounter]['serviceCharge']     = $techAllocatedRow['serviceCharge'];
      $techAllocatedArray[$arrayCounter]['remarks']           = $techAllocatedRow['remarks'];
      // To Get weather it is a Complain, New Installation Or Others : Starts
      if($techAllocatedRow['complainServiceId'] != 0)
      {
        $techAllocatedArray[$arrayCounter]['callType'] = "Complain Call";
        $techAllocatedComplainServiceId1 = $idToListIn1;
      }
      elseif($techAllocatedRow['newinstallationMasterId'] != 0)
      {
        $techAllocatedArray[$arrayCounter]['callType'] = "New Installation";
        $techAllocatedComplainServiceId2 = $idToListIn2;
      }
      else
      {
        $techAllocatedArray[$arrayCounter]['callType'] = "Others";
        $techAllocatedComplainServiceId3 = $idToListIn3;
      }
      // To Get weather it is a Complain, New Installation Or Others : Ends
      $techAllocatedTechnicianId = $techAllocatedRow['technicianId'];// Here we do not check that technicianId is SAME OR NOT, because if complainServiceId are different, then no meaning of checking technicianId
      $techAllocatedArray[$arrayCounter]['technicianId'] = $techAllocatedRow['technicianId'];
      $techAllocatedArray[$arrayCounter]['technicianName1'] = $techAllocatedRow['firstName']." ".$techAllocatedRow['lastName'];
      $techAllocatedArray[$arrayCounter]['technicianName2'] = "--";
      $techAllocatedArray[$arrayCounter]['technicianName3'] = "--";
      $techAllocatedArray[$arrayCounter]['technicianName4'] = "--";
      $techAllocatedArray[$arrayCounter]['technicianName5'] = "--";

      $techAllocatedArray[$arrayCounter]['slotName1'] = $techAllocatedRow['slotName'];
      $techAllocatedArray[$arrayCounter]['slotName2'] = "";
      $techAllocatedArray[$arrayCounter]['slotName3'] = "";
      $techAllocatedArray[$arrayCounter]['slotName4'] = "";
      $techAllocatedArray[$arrayCounter]['slotName5'] = "";
    }
    $rowCounter++;
  }
  ///select query for techallocation table End///
  if($arrayCounter == 0)
    $msg2 = '<tr><td align="center" colspan="10"> <h2><font color="red"><b>Record Not Found...!</b></h2></font></td></tr>';
  //// Selection of data after technician allocated : End
  ///// Select Time Slot : Start
  $selectTechnician = "SELECT timeSlotId,slotName
                         FROM timeslot";
  $selectTechnicianResult = mysql_query($selectTechnician);
  $slotArray = 0;
  $technicianRow = mysql_num_rows($selectTechnicianResult);
  while($technicianRow = mysql_fetch_array($selectTechnicianResult))
  {
    $timeSlotId[$slotArray] = $technicianRow['timeSlotId'];
    $slotName[$slotArray]   = $technicianRow['slotName'];
    $slotArray++;
  }
  ///// Select Time Slot : End
  
  include("./bottom.php");
  $smarty->assign("complainCallDetailArray",$complainCallDetailArray);
  $smarty->assign("arrayCounter",$arrayCounter);
  $smarty->assign("msg",$msg);
  $smarty->assign("timeSlotId",$timeSlotId);
  $smarty->assign("slotName",$slotName);
  $smarty->assign("techAllocatedArray",$techAllocatedArray);
  $smarty->display("techAlloEdit.tpl");
}
?>